/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package db;

import entities.Company;
import entities.Question;
import entities.Survey;
import entities.Target;
import entities.User;
import helpers.MyPolygon;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author gabrielcebrian
 */
public class DB {

    public static enum db {

        MySQL, PostgreSQL
    }
    /**
     * @param args the command line arguments
     */
    private db db_in_use;
    private Connection c;

    public db db_in_use(){
        return this.db_in_use;
    }

    public DB(db database) {
        db_in_use = database;

        try {
            if (db_in_use == db.PostgreSQL) {
                Class.forName("org.postgresql.Driver");
                c = DriverManager.getConnection("jdbc:postgresql://localhost/Survey", "postgres", "gabflo");
            } else if (db_in_use == db.MySQL) {
                Class.forName("com.mysql.jdbc.Driver").newInstance();
                c = DriverManager.getConnection("jdbc:mysql://localhost:8889/survey", "root", "root");
            }
        } catch (Exception e) {
        }
    }

    public void test() {
        try {
//            Statement s = c.createStatement();
//
//            String query = insertQuestionnaire(10, 10, 0, 0, 10, 0, 10, 10, 0, 10);
//
//            s.executeUpdate(query);
//
//            query = getQuestionnaires(2, 2);
//
//            ResultSet rs = s.executeQuery(query);
//
//            while (rs.next()) {
//                System.out.println(rs.getInt(1));
//            }
//
//            System.out.println("DONE");
        } catch (Exception e) {
            System.out.println(e);
        }
    }

    public boolean insertDB(String query) {
        boolean worked = true;
        long start = System.currentTimeMillis();

        try {
            Statement s = c.createStatement();
            s.executeUpdate(query);
//            long time = System.currentTimeMillis() - start;
            s.close();
//            System.out.println("DONE INSERTING in " + time + " ms");
        } catch (Exception e) {
            worked = false;
        }

        return worked;
    }

    public boolean queryDB(String query) {
        boolean worked = true;
        long start = System.currentTimeMillis();

        try {
            Statement s = c.createStatement();
            ResultSet rs = s.executeQuery(query);

//            long time = System.currentTimeMillis() - start;

//            System.out.println("DONE QUERY in " + time + " ms");

//            while (rs.next()) {
//                System.out.print(rs.getInt(1) + ", ");
//            }
            s.close();
            
//            System.out.println("DONE PRINTING RESULTS");
        } catch (Exception e) {
            worked = false;
        }

        return worked;
    }

    public String getQuestionnaires(double x, double y) {
        String query = "";

        if (db_in_use == db.PostgreSQL) {
            query = "SELECT questionnaireid FROM \"questionnaire\" WHERE geom @> point '(" + x + "," + y + ")'";
        } else if (db_in_use == db.MySQL) {
            query = "SELECT id FROM `surveyA`.`questionnaires` WHERE MBRWithin(GeomFromText('Point(" + x + " " + y + ")'),geom)";
        }

        queryDB(query);

        return query;
    }

    public String insertUser(int userid, String password, String username, String email, int age, double lat, double lng) {
        String query = "";

        if (db_in_use == db.PostgreSQL) {
            query = "INSERT INTO \"Users\"(userid, \"password\", username, email, age, \"location\") VALUES (" + userid + ", '" + password + "', '" + username + "', '" + email + "', " + age + ", '(" + lat + "," + lng + ")')";
        } else if (db_in_use == db.MySQL) {
            query = "INSERT INTO `surveyA`.`users` (`id`,`login`, `crypted_password`, `age`, `location`) VALUES (" + userid + ", '" + username + "', '" + password + "', " + age + ", GeomFromText('Point(" + lat + " " + lng + ")'))";
        }

        insertDB(query);

        return query;
    }

    public String insertQuestionnaire(int qid, int cid, MyPolygon polygon) {
        String query = "";

        if (db_in_use == db.PostgreSQL) {
            query = "INSERT INTO questionnaire(questionnaireid, start_date, end_date, geom, company_id) VALUES (" + qid + ", NULL, NULL, '"+polygon.forPostgre()+"', " + cid + ")";
        } else if (db_in_use == db.MySQL) {
            query = "INSERT INTO `surveyA`.`questionnaires` (`id`, `start_date`, `end_date`, `company_id`,`geom`) VALUES (" + qid + ",NOW(), date_add(NOW(), interval 10 day), '" + cid + "', GeomFromText('"+polygon+"'))";
        }

        insertDB(query);

        return query;
    }

    public String getUsers(MyPolygon poly) {
        String query = "";

        if (db_in_use == db.PostgreSQL) {
            query = "SELECT userid FROM \"Users\" WHERE location <@ polygon '"+poly.forPostgre()+"'";
        } else if (db_in_use == db.MySQL) {
            query = "SELECT id FROM `surveyA`.`users` WHERE MBRContains(GeomFromText('"+poly+"'),location)";
        }

        queryDB(query);

        return query;
    }
    //--------------------- sql string generators -----------
    public int lastUserId = 1;
    public int lastCompanyId = 1;
    public int lastQuestionId = 1;
    public int lastRaceId = 1;
    public int lastCompanySurveyId = 1;
    public int lastQuestionSurveyId = 1;
    public int lastSurveyId = 1;
    public int lastTargetId = 1;
    public int lastUserAnswerId = 1;
    public int lastUserSurveyId = 1;

    public static Connection getConnection() throws Exception {
        String driver = "oracle.jdbc.driver.OracleDriver";
        String url = "jdbc:oracle:thin:@localhost:1521:databaseName";
        String username = "name";
        String password = "password";
        Class.forName(driver);
        Connection conn = DriverManager.getConnection(url, username, password);
        return conn;
    }

    public String UserInsert(User u) {
        String sql = "INSERT INTO user (`userid`,`email`,`password`,`age`,`Race_raceId`,`location`,`firstName`,`lastName`) VALUES (?,?,?,?,?,GeomFromText(?),?,?);";
        try {
            PreparedStatement p = this.c.prepareStatement(sql);

            p.setInt(1, lastUserId);
            p.setString(2, u.getEmail());
            p.setString(3, u.getPassword());
            p.setInt(4, u.getAge());
            p.setInt(5, u.getRace());     
            p.setString(6, u.getLocation().toString());
            p.setString(7, u.getLogin());
            p.setString(8, u.getLogin());

            lastUserId ++;

            return p.toString().split(": ")[1];

        } catch (SQLException ex) {
            Logger.getLogger(DB.class.getName()).log(Level.SEVERE, null, ex);
        }
        return sql;
    }

    public String CompanyInsert(Company c) {
        String companyId = lastCompanyId + "", username = c.getUsername(), password = c.getPassword();

        String sql = "INSERT INTO Company (`companyId`,`username`,`password`) VALUES (?,?,?);";
        try {
            PreparedStatement p = this.c.prepareStatement(sql);
            p.setInt(1, lastCompanyId);
            p.setString(2, c.getUsername());
            p.setString(3, c.getPassword());
            lastCompanyId++;
            return p.toString().split(": ")[1];

        } catch (SQLException ex) {
            Logger.getLogger(DB.class.getName()).log(Level.SEVERE, null, ex);
        }

        return sql;
    }

    public String QuestionInsert(Question q) {
        String sql = "INSERT INTO Question (`idQuestion`,`ans_1`,`ans_2`,`ans_3`,`ans_4`,`ans_5`,`question`,`desc`) VALUES (?,?,?,?,?,?,?,?);";
        try {
            PreparedStatement p = this.c.prepareStatement(sql);
            p.setInt(1, lastQuestionId);
            p.setString(2, q.getAnswers()[0]);
            p.setString(3, q.getAnswers()[1]);
            p.setString(4, q.getAnswers()[2]);
            p.setString(5, q.getAnswers()[3]);
            p.setString(6, q.getAnswers()[4]);
            p.setString(7, q.getQuestion());
            p.setString(8, q.getDescription());

            lastQuestionId++;
            return p.toString().split(": ")[1];
            
        } catch (SQLException ex) {
            Logger.getLogger(DB.class.getName()).log(Level.SEVERE, null, ex);
        }
        return sql;
    }

    public String RaceInsert(String desc) {
        String sql = "INSERT INTO Race (`raceId`,`desc`) VALUES (?,?);";
        try {
            PreparedStatement p = this.c.prepareStatement(sql);
            p.setInt(1, lastRaceId);
            p.setString(2, desc);

            lastRaceId++;
            return p.toString().split(": ")[1];
            
        } catch (SQLException ex) {
            Logger.getLogger(DB.class.getName()).log(Level.SEVERE, null, ex);
        }
        return sql;
    }

    public String CompanySurveyInsert(Company c, Survey s) {
        String sql = "INSERT INTO companysurvey (`idCompanySurvey`,`Company_companyId`,`Survey_surveyId`) VALUES (?,?,?);";
        try {
            PreparedStatement p = this.c.prepareStatement(sql);
            p.setInt(1, lastCompanySurveyId);
            p.setInt(2, c.getId());
            p.setInt(3, s.getId());

            lastCompanySurveyId++;
            return p.toString().split(": ")[1];
            
        } catch (SQLException ex) {
            Logger.getLogger(DB.class.getName()).log(Level.SEVERE, null, ex);
        }
        return sql;
    }

    public String QuestionSurveyInsert(Question q, Survey s) {
        String sql = "INSERT INTO questionsurvey (`idQuestionSurvey`,`Question_idQuestion`,`Survey_surveyId`) VALUES (?,?,?);";
        try {
            PreparedStatement p = this.c.prepareStatement(sql);
            p.setInt(1, lastQuestionSurveyId);
            p.setInt(2, q.getId());
            p.setInt(3, s.getId());

            lastQuestionSurveyId++;
            
            return p.toString().split(": ")[1];
        } catch (SQLException ex) {
            Logger.getLogger(DB.class.getName()).log(Level.SEVERE, null, ex);
        }
        return sql;
    }

    public String SurveyInsert(Survey s, Target t) {
        String sql = "INSERT INTO survey (`surveyId`,`openDate`,`closeDate`,`Target_targetId`) VALUES (?,NOW(),date_add(NOW(), interval 10 day),?);";
        try {
            PreparedStatement p = this.c.prepareStatement(sql);

            p.setInt(1, lastSurveyId);
            //p.setDate(2, s.getOpenDate());
            //p.setDate(3, s.getCloseDate());
            p.setInt(2, t.getId());

            lastSurveyId++;
            return p.toString().split(": ")[1];
        } catch (SQLException ex) {
            Logger.getLogger(DB.class.getName()).log(Level.SEVERE, null, ex);
        }
        return sql;
    }

    public String TargetInsert(Target t) {
        String sql = "INSERT INTO target (`targetId`,`age`,`Race_raceId`,`radio`,`location`) VALUES (?,?,?,?,GeomFromText(?));";
        try {
            PreparedStatement p = this.c.prepareStatement(sql);

            p.setInt(1, lastTargetId);
            p.setInt(2, t.getAge());
            p.setInt(3, t.getRace());
            p.setInt(4, t.getRadio());
            p.setString(5, t.getLocation().toString());

            lastTargetId++;
            return p.toString().split(": ")[1];
        } catch (SQLException ex) {
            Logger.getLogger(DB.class.getName()).log(Level.SEVERE, null, ex);
        }
        return sql;
    }

    public String UserAnswerInsert(User u, Question q, Survey s, int answer) {
        String sql = "INSERT INTO useranswers (`idUserAnswers`,`Question_idQuestion`,`answer`,`Survey_surveyId`,`User_userid`) VALUES (?,?,?,?,?);";

        try {
            PreparedStatement p = this.c.prepareStatement(sql);

            p.setInt(1, lastUserAnswerId);
            p.setInt(2, q.getId());
            p.setInt(3, answer);
            p.setInt(4, s.getId());
            p.setInt(5, u.getId());

            lastUserAnswerId++;
            return p.toString().split(": ")[1];
        } catch (SQLException ex) {
            Logger.getLogger(DB.class.getName()).log(Level.SEVERE, null, ex);
        }

        return sql;
    }

    public String UserSurveyInsert(User u, Survey s) {
        String sql = "INSERT INTO usersurvey (`idUserSurvey`,`User_userid`,`Survey_surveyId`) VALUES (?,?,?);";

        try {
            PreparedStatement p = this.c.prepareStatement(sql);

            p.setInt(1, lastUserSurveyId);
            p.setInt(2, u.getId());
            p.setInt(3, s.getId());

            lastUserSurveyId++;
            return p.toString().split(": ")[1];
        } catch (SQLException ex) {
            Logger.getLogger(DB.class.getName()).log(Level.SEVERE, null, ex);
        }
        return sql;
    }
}
